<?php
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel.php");
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel/Writer/Excel2007.php");

class Silk_Zgb_Block_Report_Sales extends Mage_Core_Block_Template
{
    public function getTeamCustomer()
    {
        $collection = Mage::getModel('customer/customer')->getCollection()
            ->addAttributeToFilter('group_id', array('eq'=>4));

        $ids = array(0);
        foreach($collection as $customer)
        {
            $ids[] = $customer->getId();
        }
        return $ids;
    }

    public function getItemCollection()
    {
        $startDate = $this->getRequest()->getParam('start_date');
        $endDate = $this->getRequest()->getParam('end_date');
        $websiteId = $this->getRequest()->getParam('website_id');
        $productName = $this->getRequest()->getParam('product_name');

        $export = $this->getRequest()->getParam('export');
        $apply = $this->getRequest()->getParam('apply');

        $session = Mage::getSingleton('admin/session');

        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
        }

        $ids = array();
        $storeIds = array();

        $teams = implode(',',$this->getTeamCustomer());

        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');

        $sql = "SELECT o.website_name,oi.name,oi.unit,price,SUM(qty) as qty,SUM(price*qty) as row_total FROM zgb_order o 
        LEFT JOIN zgb_order_item oi ON o.entity_id=oi.`order_id` where o.status in ('complete','complete_order') ";
        

        if($startDate)
        {
            $startDate = "{$startDate} 00:00:00";
            $timestamp = strtotime($startDate)-(8*3600);
            $startDate = date('Y-m-d H:i:s', $timestamp);
            $sql .= " and o.created_at>= '{$startDate}'";
        }

        if($endDate)
        {
            $endDate = "{$endDate} 23:59:59";
            $timestamp = strtotime($endDate)-(8*3600);
            $endDate = date('Y-m-d H:i:s', $timestamp);
            $sql .= " and o.created_at<= '{$endDate}'";
        }

        if($websiteId)
        {

            $sql .= " and o.website_id='{$websiteId}'";

        }

        if($teams)
        {
            $sql .= " and o.customer_id not in ({$teams})";
        }

        if($productName)
        {
            $sql .= " and oi.name like '%{$productName}%'";
        }

        $sql .= " GROUP BY o.website_id,oi.`product_id`";

        //echo $sql;
        if($export || $apply)
        {
            $query = $db->query($sql);
            $items = $query->fetchAll();
        }
        else
        {
            $items = array();
        }

        return $items;
    }

    public function exportToExcel()
    {
        $items = $this->getItemCollection();
        $objPHPExcel  = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->setTitle("销售统计报表");
        $sheet->getColumnDimension('A')->setWidth(13);
        $sheet->getColumnDimension('B')->setWidth(50);

        $sheet->getStyle('A1')->getFont()->setSize(13);
        $sheet->getStyle('A1')->getFont()->setBold(true);

        $sheet->getStyle('B1')->getFont()->setSize(13);
        $sheet->getStyle('B1')->getFont()->setBold(true);

        $sheet->getStyle('C1')->getFont()->setSize(13);
        $sheet->getStyle('C1')->getFont()->setBold(true);

        $sheet->getStyle('D1')->getFont()->setSize(13);
        $sheet->getStyle('D1')->getFont()->setBold(true);

        $sheet->getStyle('E1')->getFont()->setSize(13);
        $sheet->getStyle('E1')->getFont()->setBold(true);

        $sheet->getStyle('F1')->getFont()->setSize(13);
        $sheet->getStyle('F1')->getFont()->setBold(true);


        $sheet->getRowDimension('1')->setRowHeight(20);

        $styleThinBlackBorderOutline = array(
            'borders' => array (
               'outline' => array (
                  'style' => PHPExcel_Style_Border::BORDER_THIN, 
                  'color' => array ('argb' => 'FF000000'),
              ),
           ),
        );

        $row = 1;
        $sheet->setCellValue("A{$row}","区域");
        $sheet->setCellValue("B{$row}","商品名称");
        $sheet->setCellValue("C{$row}","单位");
        $sheet->setCellValue("D{$row}","单价");
        $sheet->setCellValue("E{$row}","数量");
        $sheet->setCellValue("F{$row}","合计");

        $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
        $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);
        
        $row++;
        foreach($items as $item)
        {

            $sheet->setCellValue("A{$row}",$item['website_name']);
            $sheet->setCellValue("B{$row}",$item['name']);
            $sheet->setCellValue("C{$row}",$item['unit']);
            $sheet->setCellValue("D{$row}",$item['price']);
            $sheet->setCellValue("E{$row}",$item['qty']);
            $sheet->setCellValue("F{$row}",$item['row_total']);
            

            $sheet->getStyle("A{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("B{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("C{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("D{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("E{$row}")->applyFromArray($styleThinBlackBorderOutline);
            $sheet->getStyle("F{$row}")->applyFromArray($styleThinBlackBorderOutline);

            $row++;
        }
        $sheet->setCellValue("E{$row}", "=SUM(E2:E".($row-1).")");
        $sheet->setCellValue("F{$row}", "=SUM(F2:F".($row-1).")");
        return $objWriter;
    }
}